from sqlalchemy import create_engine, and_, or_
from sqlalchemy import Column, Integer, String
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy.orm import sessionmaker
import json

engine = create_engine(url='mysql+pymysql://root:123!#Qw@localhost/wangzhe?charset=utf8',
                       echo=False
                       )
# 创建session，类似与游标cursor
session = sessionmaker(bind=engine)()
# 生成orm基类
Base = declarative_base()


class User(Base):
    __tablename__ = 'user'
    id = Column(Integer, primary_key=True)
    name = Column(String(32))
    pwd = Column(String(32))

    def __repr__(self):
        res_dict = dict()
        res_dict['id'] = self.id
        res_dict['name'] = self.name
        res_dict['pwd'] = self.pwd
        return json.dumps(res_dict, ensure_ascii=False)


class Person(Base):
    __tablename__ = 'person'
    id = Column(Integer, primary_key=True)
    name = Column(String(100))
    age = Column(Integer)
    sex = Column(String(50))
    country = Column(String(100))
    job = Column(Integer)

    def __repr__(self):
        res_dict = dict()
        res_dict['id'] = self.id
        res_dict['name'] = self.name
        res_dict['age'] = self.age
        res_dict['sex'] = self.sex
        res_dict['country'] = self.country
        res_dict['job'] = self.job
        return json.dumps(res_dict, ensure_ascii=False)


# 创建表结构
Base.metadata.create_all(engine)

res = session.query(Person).filter(Person.name == '周瑜').count()
print(res)

